﻿using System;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Data;
using TimeTravel.Components;
using TimeTravel.Extentions;
using TimeTravel.IData;

namespace TimeTravel.SqlServer
{
    public class NavMenuData : INavMenuData
    {
        #region Fields
        private static readonly SqlHelper DbHelper = new SqlHelper();
        private static readonly string[] Columns = { "[id]", "[text]", "[linkurl]", "[fatherid]", "[target]", "[sort]" }; 
        #endregion

        #region Implementation of IDataBase<NavMenuInfo>

        /// <summary>
        /// 添加记录
        /// </summary>
        public int Add(NavMenuInfo navMenu)
        {
            if (navMenu == null) return 0;
            var strSql = new StringBuilder();
            strSql.Append("insert into NavMenu(");
            strSql.Append("[text],[fatherid],[linkurl],[sort],[target])");
            strSql.Append(" values ");
            strSql.Append("(@text,@fatherid,@linkurl,@sort,@target)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
                                            new SqlParameter("@text", SqlDbType.NVarChar,50),
                                            new SqlParameter("@fatherid", SqlDbType.Int),
                                            new SqlParameter("@linkurl", SqlDbType.NVarChar,150),
                                            new SqlParameter("@sort", SqlDbType.Int),
                                            new SqlParameter("@target", SqlDbType.NVarChar,50)
                                        };
            parameters[0].Value = navMenu.Text;
            parameters[1].Value = navMenu.FatherID;
            parameters[2].Value = navMenu.LinkUrl;
            parameters[3].Value = navMenu.Sort;
            parameters[4].Value = navMenu.Target;
            var result = DbHelper.ExecuteScalar(CommandType.Text, strSql.ToString(), parameters);
            return result == null ? 0 : Convert.ToInt32(result);

        }

        /// <summary>
        /// 删除记录（递归删除）
        /// </summary>
        /// <param name="id"></param>
        public void Delete(int id)
        {
            if (id == 0) return;
            var strSql = new StringBuilder();
            strSql.Append("with my1 as(select * from NavMenu where id = @id ");
            strSql.Append("union all select NavMenu.* from my1, NavMenu where my1.id = NavMenu.fatherid) ");
            strSql.Append("update NavMenu set deleteflag=1 ");
            strSql.Append("where exists");
            strSql.Append("(select id from my1 where my1.id = NavMenu.id); ");
            SqlParameter[] parameters = {
                                            new SqlParameter("@id", SqlDbType.Int)
                                        };
            parameters[0].Value = id;
            DbHelper.ExecuteNonQuery(CommandType.Text, strSql.ToString(), parameters);
        }

        /// <summary>
        /// 更新记录
        /// </summary>
        /// <param name="navMenu"></param>
        public void Update(NavMenuInfo navMenu)
        {
            if (navMenu == null) return;
            var strSql = new StringBuilder();
            strSql.Append("update NavMenu set ");
            strSql.Append("[text]=@text,");
            strSql.Append("[linkurl]=@linkurl,");
            strSql.Append("[target]=@target,");
            strSql.Append("[sort]=@sort,");
            strSql.Append("[fatherid]=@fatherid ");
            strSql.Append("where [id]=@id ;");
            SqlParameter[] parameters = {
                                            new SqlParameter("@text", SqlDbType.NVarChar, 50),
                                            new SqlParameter("@fatherid", SqlDbType.Int),
                                            new SqlParameter("@linkurl", SqlDbType.NVarChar, 150),
                                            new SqlParameter("@sort", SqlDbType.Int),
                                            new SqlParameter("@target", SqlDbType.NVarChar, 50),
                                            new SqlParameter("@id", SqlDbType.Int)
                                        };
            parameters[0].Value = navMenu.Text;
            parameters[1].Value = navMenu.FatherID;
            parameters[2].Value = navMenu.LinkUrl;
            parameters[3].Value = navMenu.Sort;
            parameters[4].Value = navMenu.Target;
            parameters[5].Value = navMenu.ID;
            DbHelper.ExecuteNonQuery(CommandType.Text, strSql.ToString(), parameters);
        }

        /// <summary>
        /// 获取所有
        /// </summary>
        /// <returns></returns>
        public IList<NavMenuInfo> GetAll()
        {
            var strSql = new StringBuilder();
            strSql.AppendFormat("select {0} from NavMenu ",String.Join(",",Columns));
            strSql.Append("where DeleteFlag = 0;");
            var result = DbHelper.ExecuteObjectList(strSql.ToString(), DataReaderParser);
            return result.ToList();
        }

        /// <summary>
        /// 根据条件获取
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public RecordSet<NavMenuInfo> GetBySearch(PageQueryInfo query)
        {
            return query == null ? 
                new RecordSet<NavMenuInfo>() 
                : 
                DbHelper.ExecutePaging("NavMenu",query, DataReaderParser,Columns);
        }

        /// <summary>
        /// 获取详细信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public NavMenuInfo GetOne(int id)
        {
            if (id == 0) return null;
            var strSql = new StringBuilder();
            strSql.AppendFormat("select {0} from NavMenu ",String.Join(",",Columns));
            strSql.Append("where [id]=@id;");
            SqlParameter[] parameters = {
                                            new SqlParameter("@id", SqlDbType.Int)
                                        };
            parameters[0].Value = id;
            return DbHelper.ExecuteObject(CommandType.Text, strSql.ToString(), DataReaderParser, parameters);
        }

        /// <summary>
        /// 获取总数
        /// </summary>
        /// <returns></returns>
        public int GetTotalCount()
        {
            var strSql = new StringBuilder();
            strSql.Append("select count(id) from NavMenu ");
            strSql.Append("where DeleteFlag = 0;");
            var result = DbHelper.ExecuteScalar(CommandType.Text, strSql.ToString());
            return result == null ? 0 : Convert.ToInt32(result);
        }

        #endregion

        #region BuildNavMenuObject

        /// <summary>
        /// 从DataReader中构建对象
        /// </summary>
        /// <param name="reader"></param>
        /// <returns></returns>
        private NavMenuInfo DataReaderParser(IDataReader reader)
        {
            var menuInfo = new NavMenuInfo
                               {
                                   ID = reader.GetInt32("id"),
                                   FatherID = reader.GetInt32("fatherid"),
                                   Text = reader.GetString("text"),
                                   LinkUrl = reader.GetString("linkurl"),
                                   Target = reader.GetString("target"),
                                   Sort = reader.GetInt32("sort")
                               };
            return menuInfo;
        }

        #endregion
    }
}

